'''
5.4.2 Pandas自动操作Excel
'''

import pandas as pd

# Pandas将数据写入DataFrame

# 通过字典形式构建DataFrame
df = pd.DataFrame({
    'id': [1,2,3],
    'name': ['张三', '李四', '王五'],
    'age': [28, 25, 30]
})
# 自定义索引
df = df.set_index('id')
df.to_excel('people.xlsx')

filepath = 'people.xlsx'
people = pd.read_excel(filepath, sheet_name='Sheet1')
# 输出列名
print(people.columns)

# header=2表示从第三行开始，相当于跳过了前两行
people1 = pd.read_excel(filepath, header=2, sheet_name='Sheet1')
# 输出列名
print(people1.columns)

# 指定id列为索引
people2 = pd.read_excel(filepath, index_col='id')
print(people2.columns)

# skiprows开头跳过几行，usecols使用那些列的数据，dtype设置某一列数据的类型
people3 = pd.read_excel(filepath, skiprows=4, usecols='E:H', dtype={'ID': str, 'gender': str, 'birthday': str})
print(people3.columns)

# 左表转为右表

import pandas as pd
from datetime import date, timedelta

# 开始日期
startday = date(2019,7,1)

# 读入工作薄
people = pd.read_excel('people.xlsx', skiprows=4, usecols='B:E', dtype={'ID': str, 'gender': str, 'birthday': str})

for i in people.index:
    people.at[i, 'ID'] = i + 1
    # 判断性别
    people.at[i,'gender'] = 'Male' if i % 2 == 0 else 'Female'
    # 计算生日时间
    people.at[i,'birthday'] = date(startday.year + i, startday.month, startday.day)

# inplace表示就地修改 DataFrame，不必再重新创建一个新的DataFrame来存储修改后的状态
people.set_index('ID', inplace=True)

print(people)

people.to_excel('people2.xlsx')